﻿using System;
using System.Data;
using System.Data.SqlClient;
using Entities;

namespace DAL
{
    public class DAL_Product
    {
        public const string usp_SelectGoodProduct             = "usp_SelectGoodProduct";
        public const string usp_SelectProduct                 = "usp_SelectProduct";
        public const string usp_SelectProductsByAndID_MenuURL = "usp_SelectProductsByAndID_MenuURL";
        public const string usp_SelectProductsDynamic         = "usp_SelectProductsDynamic";
        public const string usp_InsertUpdateProduct           = "usp_InsertUpdateProduct";
        public const string usp_SelectProductsMaxID           = "usp_SelectProductsMaxID";
        public const string usp_DeleteProduct                 = "usp_DeleteProduct";
        public const string usp_SelectProductsAll             = "usp_SelectProductsAll";
        public const string usp_SelectProductsAll_IsDisplay   = "usp_SelectProductsAll_IsDisplay";
        public const string usp_SelectProductsTopNew          = "usp_SelectProductsTopNew";

        public DataTable Get_Product_All()
        {
            DataTable dt = new DataTable();
            SqlDataAdapter sqlDataAdapter;
            SqlCommand sqlCommand = new SqlCommand(usp_SelectProductsAll, Utils.sqlConnection);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlDataAdapter = new SqlDataAdapter(sqlCommand);
            try
            {
                Utils.sqlConnection.Open();
                sqlDataAdapter.Fill(dt);
                Utils.sqlConnection.Close();
            }
            catch
            {
                if (Utils.sqlConnection.State == ConnectionState.Open)
                {
                    Utils.sqlConnection.Close();
                }
                return dt;
            }
            return dt;
        }

        public DataTable Get_Product_All_IsDisplay()
        {
            DataTable dt = new DataTable();
            SqlDataAdapter sqlDataAdapter;
            SqlCommand sqlCommand = new SqlCommand(usp_SelectProductsAll_IsDisplay, Utils.sqlConnection);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlDataAdapter = new SqlDataAdapter(sqlCommand);
            try
            {
                Utils.sqlConnection.Open();
                sqlDataAdapter.Fill(dt);
                Utils.sqlConnection.Close();
            }
            catch
            {
                if (Utils.sqlConnection.State == ConnectionState.Open)
                {
                    Utils.sqlConnection.Close();
                }
                return dt;
            }
            return dt;
        }

        public DataTable Get_Product_TopNew()
        {
            DataTable dt = new DataTable();
            SqlDataAdapter sqlDataAdapter;
            SqlCommand sqlCommand = new SqlCommand(usp_SelectProductsTopNew, Utils.sqlConnection);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlDataAdapter = new SqlDataAdapter(sqlCommand);
            try
            {
                Utils.sqlConnection.Open();
                sqlDataAdapter.Fill(dt);
                Utils.sqlConnection.Close();
            }
            catch
            {
                if (Utils.sqlConnection.State == ConnectionState.Open)
                {
                    Utils.sqlConnection.Close();
                }
                return dt;
            }
            return dt;
        }

        public DataTable Get_GoodProduct()
        {
            DataTable dt = new DataTable();
            SqlDataAdapter sqlDataAdapter;
            SqlCommand sqlCommand = new SqlCommand(usp_SelectGoodProduct, Utils.sqlConnection);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlDataAdapter = new SqlDataAdapter(sqlCommand);
            try
            {
                Utils.sqlConnection.Open();
                sqlDataAdapter.Fill(dt);
                Utils.sqlConnection.Close();
            }
            catch
            {
                return dt;
            }
            return dt;
        }

        public DataTable Get_Product_ID_MenuURL(int pID)
        {
            DataTable dt = new DataTable();
            SqlDataAdapter sqlDataAdapter;
            SqlParameter[] sqlParameter = new SqlParameter[1];
            SqlCommand sqlCommand = new SqlCommand(usp_SelectProductsByAndID_MenuURL, Utils.sqlConnection);
            sqlCommand.CommandType = CommandType.StoredProcedure;

            sqlParameter[0] = new SqlParameter();
            sqlParameter[0].DbType = DbType.Int32;
            sqlParameter[0].Direction = ParameterDirection.Input;
            sqlParameter[0].ParameterName = "@ID_MenuURL";
            sqlParameter[0].Value = pID;

            sqlCommand.Parameters.AddRange(sqlParameter);
            sqlDataAdapter = new SqlDataAdapter(sqlCommand);
            try
            {
                Utils.sqlConnection.Open();
                sqlDataAdapter.Fill(dt);
                Utils.sqlConnection.Close();
            }
            catch
            {
                if (Utils.sqlConnection.State == ConnectionState.Open)
                {
                    Utils.sqlConnection.Close();
                }
                return dt;
            }
            return dt;
        }

        public DataTable Get_Product(Int64 pID)
        {
            DataTable dt = new DataTable();
            SqlDataAdapter sqlDataAdapter;
            SqlParameter[] sqlParameter = new SqlParameter[1];
            SqlCommand sqlCommand = new SqlCommand(usp_SelectProduct, Utils.sqlConnection);
            sqlCommand.CommandType = CommandType.StoredProcedure;

            sqlParameter[0] = new SqlParameter();
            sqlParameter[0].DbType = DbType.Int32;
            sqlParameter[0].Direction = ParameterDirection.Input;
            sqlParameter[0].ParameterName = "@ID";
            sqlParameter[0].Value = pID;

            sqlCommand.Parameters.AddRange(sqlParameter);
            sqlDataAdapter = new SqlDataAdapter(sqlCommand);
            try
            {
                Utils.sqlConnection.Open();
                sqlDataAdapter.Fill(dt);
                Utils.sqlConnection.Close();
            }
            catch
            {
                if (Utils.sqlConnection.State == ConnectionState.Open)
                {
                    Utils.sqlConnection.Close();
                }
                return dt;
            }
            return dt;
        }

        public DataTable Get_Product_Dynamic(string pCondition)
        {
            DataTable dt = new DataTable();
            SqlDataAdapter sqlDataAdapter;
            SqlParameter[] sqlParameter = new SqlParameter[1];
            SqlCommand sqlCommand = new SqlCommand(usp_SelectProductsDynamic, Utils.sqlConnection);
            sqlCommand.CommandType = CommandType.StoredProcedure;

            sqlParameter[0] = new SqlParameter();
            sqlParameter[0].DbType = DbType.String;
            sqlParameter[0].Direction = ParameterDirection.Input;
            sqlParameter[0].ParameterName = "@WhereCondition";
            sqlParameter[0].Value = pCondition;

            sqlCommand.Parameters.AddRange(sqlParameter);
            sqlDataAdapter = new SqlDataAdapter(sqlCommand);
            try
            {
                Utils.sqlConnection.Open();
                sqlDataAdapter.Fill(dt);
                Utils.sqlConnection.Close();
            }
            catch
            {
                if (Utils.sqlConnection.State == ConnectionState.Open)
                {
                    Utils.sqlConnection.Close();
                }
                return dt;
            }
            return dt;
        }

        public bool InsertUpdate_Product(Product pProduct)
        {
            SqlParameter[] sqlParameter = new SqlParameter[16];
            SqlCommand sqlCommand = new SqlCommand(usp_InsertUpdateProduct, Utils.sqlConnection);
            sqlCommand.CommandType = CommandType.StoredProcedure;

            sqlParameter[0] = new SqlParameter();
            sqlParameter[0].DbType = DbType.Int64;
            sqlParameter[0].Direction = ParameterDirection.Input;
            sqlParameter[0].ParameterName = "@ID";
            sqlParameter[0].Value = pProduct.ID;

            sqlParameter[1] = new SqlParameter();
            sqlParameter[1].DbType = DbType.Int32;
            sqlParameter[1].Direction = ParameterDirection.Input;
            sqlParameter[1].ParameterName = "@ID_MENUURL";
            sqlParameter[1].Value = pProduct.ID_MENUURL;

            sqlParameter[2] = new SqlParameter();
            sqlParameter[2].DbType = DbType.String;
            sqlParameter[2].Direction = ParameterDirection.Input;
            sqlParameter[2].ParameterName = "@CODE";
            sqlParameter[2].Value = pProduct.CODE;

            sqlParameter[3] = new SqlParameter();
            sqlParameter[3].DbType = DbType.String;
            sqlParameter[3].Direction = ParameterDirection.Input;
            sqlParameter[3].ParameterName = "@NAME";
            sqlParameter[3].Value = pProduct.NAME;

            sqlParameter[4] = new SqlParameter();
            sqlParameter[4].DbType = DbType.String;
            sqlParameter[4].Direction = ParameterDirection.Input;
            sqlParameter[4].ParameterName = "@DESCRIPTION";
            sqlParameter[4].Value = pProduct.DESCRIPTION;

            sqlParameter[5] = new SqlParameter();
            sqlParameter[5].DbType = DbType.Int64;
            sqlParameter[5].Direction = ParameterDirection.Input;
            sqlParameter[5].ParameterName = "@PRICE";
            sqlParameter[5].Value = pProduct.PRICE;

            sqlParameter[6] = new SqlParameter();
            sqlParameter[6].DbType = DbType.DateTime;
            sqlParameter[6].Direction = ParameterDirection.Input;
            sqlParameter[6].ParameterName = "@CREATE_DATE";
            sqlParameter[6].Value = pProduct.CREATE_DATE;

            sqlParameter[7] = new SqlParameter();
            sqlParameter[7].DbType = DbType.String;
            sqlParameter[7].Direction = ParameterDirection.Input;
            sqlParameter[7].ParameterName = "@IMAGE_DIRECTORY";
            sqlParameter[7].Value = pProduct.IMAGE_DIRECTORY;

            sqlParameter[8] = new SqlParameter();
            sqlParameter[8].DbType = DbType.String;
            sqlParameter[8].Direction = ParameterDirection.Input;
            sqlParameter[8].ParameterName = "@STAT_STOCK";
            sqlParameter[8].Value = pProduct.STAT_STOCK;

            sqlParameter[9] = new SqlParameter();
            sqlParameter[9].DbType = DbType.String;
            sqlParameter[9].Direction = ParameterDirection.Input;
            sqlParameter[9].ParameterName = "@OTHER_NAME";
            sqlParameter[9].Value = pProduct.OTHER_NAME;

            sqlParameter[10] = new SqlParameter();
            sqlParameter[10].DbType = DbType.String;
            sqlParameter[10].Direction = ParameterDirection.Input;
            sqlParameter[10].ParameterName = "@ORIGIN";
            sqlParameter[10].Value = pProduct.ORIGIN;

            sqlParameter[11] = new SqlParameter();
            sqlParameter[11].DbType = DbType.String;
            sqlParameter[11].Direction = ParameterDirection.Input;
            sqlParameter[11].ParameterName = "@STORAGE_TIME";
            sqlParameter[11].Value = pProduct.STORAGE_TIME;

            sqlParameter[12] = new SqlParameter();
            sqlParameter[12].DbType = DbType.Decimal;
            sqlParameter[12].Direction = ParameterDirection.Input;
            sqlParameter[12].ParameterName = "@SALE";
            sqlParameter[12].Value = pProduct.SALE;

            sqlParameter[13] = new SqlParameter();
            sqlParameter[13].DbType = DbType.String;
            sqlParameter[13].Direction = ParameterDirection.Input;
            sqlParameter[13].ParameterName = "@IS_DISPLAY";
            sqlParameter[13].Value = pProduct.IS_DISPLAY;

            sqlParameter[14] = new SqlParameter();
            sqlParameter[14].DbType = DbType.Int32;
            sqlParameter[14].Direction = ParameterDirection.Input;
            sqlParameter[14].ParameterName = "@WEIGHT";
            sqlParameter[14].Value = pProduct.WEIGHT;

            sqlParameter[15] = new SqlParameter();
            sqlParameter[15].DbType = DbType.Int32;
            sqlParameter[15].Direction = ParameterDirection.Input;
            sqlParameter[15].ParameterName = "@SEQUENCE";
            sqlParameter[15].Value = pProduct.SEQUENCE;

            sqlCommand.Parameters.AddRange(sqlParameter);
            try
            {
                Utils.sqlConnection.Open();
                sqlCommand.ExecuteNonQuery();
                Utils.sqlConnection.Close();
            }
            catch
            {
                if (Utils.sqlConnection.State == ConnectionState.Open)
                {
                    Utils.sqlConnection.Close();
                }
                return false;
            }
            return true;
        }

        public Int64 Get_Product_MaxID()
        {
            Int64 intReturn=0;
            SqlCommand sqlCommand = new SqlCommand(usp_SelectProductsMaxID, Utils.sqlConnection);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            try
            {
                Utils.sqlConnection.Open();
                intReturn=(Int64)sqlCommand.ExecuteScalar();
                Utils.sqlConnection.Close();
            }
            catch
            {
                if (Utils.sqlConnection.State == ConnectionState.Open)
                {
                    Utils.sqlConnection.Close();
                }
                return intReturn;
            }
            return intReturn;
        }

        public bool DeleteProduct(Int64 pID)
        {
            SqlParameter[] sqlParameter = new SqlParameter[1];
            SqlCommand sqlCommand = new SqlCommand(usp_DeleteProduct, Utils.sqlConnection);
            sqlCommand.CommandType = CommandType.StoredProcedure;

            sqlParameter[0] = new SqlParameter();
            sqlParameter[0].DbType = DbType.Int64;
            sqlParameter[0].Direction = ParameterDirection.Input;
            sqlParameter[0].ParameterName = "@ID";
            sqlParameter[0].Value = pID;
            sqlCommand.Parameters.AddRange(sqlParameter);
            try
            {
                Utils.sqlConnection.Open();
                sqlCommand.ExecuteNonQuery();
                Utils.sqlConnection.Close();
            }
            catch
            {
                if (Utils.sqlConnection.State == ConnectionState.Open)
                {
                    Utils.sqlConnection.Close();
                }
                return false;
            }
            return true;
        }
    }
}
